#!/usr/bin/perl

use strict;
use Win32::GUI;
use DBI;
use Encode;
use Data::Dumper;

use constant {
    PADX => 4,
    PADY => 4,
    TEXTLEN => 300,
    FONTSIZE => 12 
};

my $font = Win32::GUI::Font->new(-name => "Georgia", -size => FONTSIZE);

my $win = Win32::GUI::Window->new(
        -title => "Data Explorer",
        -name => "Main",
        -font => $font,
        -size => [800,600],
        -minsize => [300,200],
    );

my $combo = $win->AddCombobox(
    -dropdown => 1,
    -dropdownlist => 1,
    -pos => [PADX, PADY],
    -size => [TEXTLEN, FONTSIZE*2*10],
);
$combo->Change(-onChange => sub {Table_Click($combo);});

my $dbh = DBI->connect("dbi:SQLite:dbname=data/index.db3", 
                       "", ""
                      ) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM table_t WHERE t_name like 'nexans.%'");
$sth->execute();

while (my $row = $sth->fetchrow_hashref()) {
    my $tname = $row->{t_name};
    $tname =~ s/nexans\.//;

    $combo->AddString($tname . " | " 
                      . encode("euc-cn", decode("utf8",$row->{t_title})));
}

my $lvData = $win->AddListView(
    -gridlines => 1, 
    -fullrowselect => 1,
    -flatsb => 1,
    -pos => [PADX-1, FONTSIZE*2+PADY*2],
    -size => [100,100]
);

$sth->finish();
$dbh->disconnect();

$win->Center();
$win->Show();
Win32::GUI::Dialog();

sub Main_Terminate 
{
    return -1;
}
sub Main_Resize
{
    $lvData->Width($win->Width() - PADX*5 -2);
    $lvData->Height($win->Height() - $lvData->Top() - FONTSIZE*2 - PADY*4);
}

sub Table_Click
{
    my $combo = shift;

    my $index = $combo->GetCurSel();

    return if ($index == -1);

    my $tname = $combo->GetString($index);
    $tname =~ s/\s+.*//;

    my @rows = ();
    my @field_names = ();
    my $dbh = DBI->connect("dbi:SQLite:dbname=data/index.db3", 
                           "", ""
                          ) or die $DBI::errstr;
    my $sth = $dbh->prepare("SELECT * FROM field_t WHERE t_name = 'nexans.$tname'"
                            . " ORDER BY f_id");
    $sth->execute();

    while (my $row = $sth->fetchrow_hashref()) {
        push @rows, $row;
        push @field_names, $row->{f_name};
    }

    $sth->finish();
    $dbh->disconnect();

    # clear columns
    $lvData->Clear();
    while (1) {
        my $col = $lvData->GetColumn(0);
        last unless(defined($col));

        $lvData->DeleteColumn(0);
    }

    # add column headers
    for my $index (0 .. $#rows) {
        my $row = $rows[$index];

        $lvData->InsertColumn(
                  -text => encode("euc-cn", decode("utf8", $row->{f_title})),
                  -width => ($row->{f_size} == 0) ? FONTSIZE*8 
                                                  : FONTSIZE*($row->{f_size}+2),
                  );
    }

    $dbh = DBI->connect("dbi:SQLite:dbname=data/nexans.db3", 
                        "", ""
                       ) or die $DBI::errstr;

    my $fields = join(",", @field_names);

    $sth = $dbh->prepare("SELECT $fields FROM $tname");
    $sth->execute();

    while (my $row = $sth->fetchrow_arrayref()) {
        for (@{$row}) {
            $_ = encode("euc-cn", decode("utf8", $_)),
        }
        $lvData->InsertItem(-text => $row);
    }

    $sth->finish();

    $dbh->disconnect();
}

